using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Colorprj.Masters;
using System.Data.Common;
using Colorprj.Challan;
using Colorprj.Common;
namespace Colorprj.Challan
{
    class ChallanManager
    {


        public string  GetNextChallanNo()
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "getNextChallanNo";
            DbCommand GetVNoCommand = db.GetStoredProcCommand(sqlCommand);
            // db.AddInParameter(GetVNoCommand, "vchType", DbType.String, VType);
            string VNO = db.ExecuteScalar(GetVNoCommand).ToString() ;
            return VNO;

        }


        ///// <summary>
        ///// Transfers an amount between two accounts.
        ///// </summary>
        ///// <param name="transactionAmount">Amount to transfer.</param>
        ///// <param name="sourceAccount">Account to be credited.</param>
        ///// <param name="destinationAccount">Account to be debited.</param>
        ///// <returns>true if sucessful; otherwise false.</returns>
        ///// <remarks>Demonstrates executing multiple updates within the 
        ///// context of a transaction.</remarks>
        public bool AddChallan(ChallanHeadEntity phEntity)
        {
            bool result = false;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "AddChallanHead";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "ChallanNo", DbType.String, phEntity.ChallanNo);
            db.AddInParameter(creditCommand, "ChallanDate", DbType.DateTime, phEntity.ChallanDate);
            db.AddInParameter(creditCommand, "BuyerId", DbType.Int32, phEntity.BuyerId);
            db.AddInParameter(creditCommand, "TotalAmount", DbType.Double, phEntity.TotalAmount);
            db.AddInParameter(creditCommand, "Mop", DbType.String, phEntity.Mop);
            db.AddInParameter(creditCommand, "SalesManId", DbType.Int32, phEntity.SalesManId);


            List<ChallanDetailEntity> ChallanDetailList = phEntity.ChallanDetailList;


            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {
                    // Credit the first account
                    int ChallanHeadId = Convert.ToInt16(db.ExecuteScalar(creditCommand, transaction));
                    // Debit the second account
                    //db.ExecuteNonQuery(debitCommand, transaction);
                    sqlCommand = "AddChallanDetail";
                    //DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
                    foreach (ChallanDetailEntity Challan in ChallanDetailList)
                    {
                        DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
                        db.AddInParameter(debitCommand, "ChallanId", DbType.Int32, ChallanHeadId);
                        db.AddInParameter(debitCommand, "ProductId", DbType.Int32, Challan.ProductId);
                        db.AddInParameter(debitCommand, "SubproductId", DbType.Int32, Challan.SubproductId);
                        db.AddInParameter(debitCommand, "Qty", DbType.Decimal, Challan.Qty);
                        db.AddInParameter(debitCommand, "Rate", DbType.Decimal, Challan.Rate);
                        db.AddInParameter(debitCommand, "Amount", DbType.Decimal, Challan.Amount);

                        db.ExecuteNonQuery(debitCommand, transaction);

                    }
                    // Commit the transaction
                    transaction.Commit();

                    result = true;
                }
                catch (Exception e)
                {
                    // Rollback transaction
                    //throw new Exception(e.Message) ; 
                    transaction.Rollback();
                }
                connection.Close();

                return result;
            }
        }


        ///// <summary>
        ///// Transfers an amount between two accounts.
        ///// </summary>
        ///// <param name="transactionAmount">Amount to transfer.</param>
        ///// <param name="sourceAccount">Account to be credited.</param>
        ///// <param name="destinationAccount">Account to be debited.</param>
        ///// <returns>true if sucessful; otherwise false.</returns>
        ///// <remarks>Demonstrates executing multiple updates within the 
        ///// context of a transaction.</remarks>
        public bool UpdChallan(ChallanHeadEntity phEntity)
        {
            bool result = false;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "UpdChallanHead";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "ChallanNo", DbType.String, phEntity.ChallanNo);
            db.AddInParameter(creditCommand, "ChallanDate", DbType.DateTime, phEntity.ChallanDate);
            db.AddInParameter(creditCommand, "BuyerId", DbType.Int32, phEntity.BuyerId);
            db.AddInParameter(creditCommand, "ChallanId", DbType.Int32, phEntity.ChallanHeadId);
            db.AddInParameter(creditCommand, "TotalAmount", DbType.Double, phEntity.TotalAmount);
            db.AddInParameter(creditCommand, "SalesManId", DbType.String, phEntity.Mop);

            List<ChallanDetailEntity> ChallanDetailList = phEntity.ChallanDetailList;


            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {
                    // Credit the first account
                    int ChallanHeadId = Convert.ToInt16(db.ExecuteScalar(creditCommand, transaction));
                    // Debit the second account
                    //db.ExecuteNonQuery(debitCommand, transaction);
                    sqlCommand = "AddChallanDetail";
                    //DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
                    foreach (ChallanDetailEntity Challan in ChallanDetailList)
                    {
                        DbCommand debitCommand;
                        if (Challan.ChallanDetailId == 0)
                        {
                            debitCommand = db.GetStoredProcCommand(sqlCommand);
                        }
                        else
                        {
                            if (Challan.Qty == 0) { debitCommand = db.GetStoredProcCommand("DelChallanDetail"); }

                            else
                            {
                                debitCommand = db.GetStoredProcCommand("UpdChallanDetail");
                                db.AddInParameter(debitCommand, "OldQty", DbType.Decimal, Challan.OldQty);
                            }
                            db.AddInParameter(debitCommand, "ChallanDetailId", DbType.Decimal, Challan.ChallanDetailId);
                        }
                        if (Challan.Qty != 0)
                        {
                            db.AddInParameter(debitCommand, "ChallanId", DbType.Int32, phEntity.ChallanHeadId);
                            db.AddInParameter(debitCommand, "ProductId", DbType.Int32, Challan.ProductId);
                            db.AddInParameter(debitCommand, "SubproductId", DbType.Int32, Challan.SubproductId);
                            db.AddInParameter(debitCommand, "Qty", DbType.Decimal, Challan.Qty);
                            db.AddInParameter(debitCommand, "Rate", DbType.Decimal, Challan.Rate);
                            db.AddInParameter(debitCommand, "Amount", DbType.Decimal, Challan.Amount);
                        }
                        db.ExecuteNonQuery(debitCommand, transaction);

                    }
                    // Commit the transaction
                    transaction.Commit();

                    result = true;
                }
                catch (Exception e)
                {
                    // Rollback transaction
                    //throw new Exception(e.Message) ; 
                    transaction.Rollback();
                }
                connection.Close();

                return result;
            }
        }



        public List<ChallanHeadEntity> GetUnInvChallanList(int BuyerId)
        {
            bool result = false;
            List<ChallanHeadEntity> ChallanList = new List<ChallanHeadEntity>();
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "SelUnInvChallan";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "BuyerId", DbType.Int32, BuyerId);
            DataSet ChallanchaseDataSet = db.ExecuteDataSet(creditCommand);
            foreach (DataRow dr in ChallanchaseDataSet.Tables[0].Rows)
            {
                ChallanHeadEntity phEntity = new ChallanHeadEntity();
                phEntity.BuyerId = Convert.ToInt16(dr["BuyerId"]);
                phEntity.ChallanHeadId = Convert.ToInt16(dr["ChallanId"]);
                phEntity.ChallanNo = dr["ChallanNo"].ToString();
                phEntity.ChallanDate = Convert.ToDateTime(dr["ChallanDate"]);
                ChallanList.Add(phEntity); 
            }

            return ChallanList;
        }


        public ChallanHeadEntity GetChallan(int ChallanId)
        {
            bool result = false;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "SelChallanHeadDet";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "ChallanHeadId", DbType.Int32, ChallanId);

            DataSet ChallanchaseDataSet = db.ExecuteDataSet(creditCommand);
            ChallanHeadEntity phEntity = new ChallanHeadEntity();
            List<ChallanDetailEntity> ChallanDetailList = new List<ChallanDetailEntity>();
            foreach (DataRow dr in ChallanchaseDataSet.Tables[0].Rows)
            {
                phEntity.BuyerId = Convert.ToInt16(dr["BuyerId"]);
                phEntity.ChallanHeadId = Convert.ToInt16(dr["ChallanId"]);
                phEntity.ChallanNo = dr["ChallanNo"].ToString();
                phEntity.ChallanDate = Convert.ToDateTime(dr["ChallanDate"]);
                phEntity.InvoiceId = Utility.getIntVal(dr["InvoiceId"]);
                phEntity.Printed = Utility.getBoolVal(dr["Printed"]);
                phEntity.TotalAmount  = Utility.getDblVal(dr["TotalAmount"]);
                phEntity.SalesManId = Convert.ToInt16(dr["SalesManId"]);
                //phEntity.Mop  = dr["Mop"].ToString();
           
                ChallanDetailEntity pdEntity = new ChallanDetailEntity();
                pdEntity.ProductId = Convert.ToInt16(dr["ProductId"]);
                pdEntity.SubproductId = Convert.ToInt16(dr["SubproductId"]);
                pdEntity.Qty = Utility.getDblVal (dr["Qty"]);
                pdEntity.OldQty = Utility.getDblVal(dr["Qty"]);
                pdEntity.Rate = Convert.ToDouble(dr["Rate"]);
                pdEntity.Amount = Convert.ToDouble(dr["Amount"]);
                pdEntity.ChallanDetailId = Convert.ToInt16(dr["ChallanDetailId"]);
                ChallanDetailList.Add(pdEntity);
            }
            phEntity.ChallanDetailList = ChallanDetailList;
            return phEntity;
        }

        public SubProduct GetSubProductById(int Id)
        {
            SubProductManager spm = new SubProductManager();

            return spm.GetSubProductById(Id);
        }

        public int GetLastChallanNo()
        {
            int result = 0;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            string sqlCommand = "SelLastChallanNo";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            DataSet PurchaseDataSet = db.ExecuteDataSet(creditCommand);
            result = Convert.ToInt16(PurchaseDataSet.Tables[0].Rows[0]["MaxChallanId"]);

            return result;
        }

        public void UpdPrintFlg(string  ChallanNo)
        {
            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "UpdChallanWithPrintFlag";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "ChallanNo", DbType.String, ChallanNo);
            db.ExecuteNonQuery(creditCommand);
        }


    }
}
